import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

part_out_router = APIRouter(prefix="/part/partoutHead", tags=["备件领出管理"], dependencies=[Depends(oauth2_scheme)])


class SparePartTakeoutSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    ftakedateStart: Optional[str] = Field("", description="领出/退回日期开始(yyyyMMdd)")
    ftakedateEnd: Optional[str] = Field("", description="领出/退回日期结束(yyyyMMdd)")
    fbillno: Optional[str] = Field("", description="单据编号(支持前缀匹配)")
    frequuser: Optional[str] = Field("", description="领用人ID")
    fbilltype: Optional[str] = Field("", description="单据类型(1-领出/2-退回)")
    fbillstate: Optional[str] = Field("", description="单据状态")


@part_out_router.get("/list", summary="备件领出查询接口")
def list_part_out(search: SparePartTakeoutSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.s_tc_partout_head where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.frequuser != "":
        sql = sql + " and frequuser = :frequuser "
        param["frequuser"] = search.frequuser
    if search.fbilltype != "":
        sql = sql + " and fbilltype = :fbilltype "
        param["fbilltype"] = search.fbilltype
    if search.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql = sql + " and to_char(frequdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,frequuser,frequdate,fitemcount,fpiececount,ftotalamt,frecuser,"
    sql = sql + " frectelno,fsenduser,fsendtype,fsenddate,fsendno,fbilltype,fexplanation,"
    sql = sql + " fupddate,foperator,fcheckdate,fcheckuser,fbillstate"
    sql = sql + " from lims.s_tc_partout_head where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.frequuser != "":
        sql = sql + " and frequuser = :frequuser "
        param["frequuser"] = search.frequuser
    if search.fbilltype != "":
        sql = sql + " and fbilltype = :fbilltype "
        param["fbilltype"] = search.fbilltype
    if search.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql = sql + " and to_char(frequdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    if search.order == "desc":
        sql = sql + " order by  frequdate,fbillno desc "
    else:
        sql = sql + " order by  frequdate,fbillno "
    sql = sql + " limit :limit offset :offset "
    param["offset"] = search.offset
    param["limit"] = search.limit
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.fpartid,b.fmacno,b.fbuyprice,b.fqty,b.ftotamt,b.fmemo"
    sql_3 = sql_3 + " from lims.s_tc_partout_head a,lims.s_tc_partout_detail b where a.fbillno=b.fbillno "
    if search.fsiteNo != "":
        sql_3 = sql_3 + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fbillno != "":
        sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.frequuser != "":
        sql_3 = sql_3 + " and frequuser = :frequuser "
        param["frequuser"] = search.frequuser
    if search.fbilltype != "":
        sql_3 = sql_3 + " and fbilltype = :fbilltype "
        param["fbilltype"] = search.fbilltype
    if search.fbillstate != "":
        sql_3 = sql_3 + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql_3 = sql_3 + " and to_char(frequdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['frequuser'] = data[key][1]
        d1['frequdate'] = data[key][2]
        d1['fitemcount'] = data[key][3]
        d1['fpiececount'] = data[key][4]
        d1['ftotalamt'] = data[key][5]
        d1['frecuser'] = data[key][6]
        d1['frectelno'] = data[key][7]
        d1['fsenduser'] = data[key][8]
        d1['fsendtype'] = data[key][9]
        d1['fsenddate'] = data[key][10]
        d1['fsendno'] = data[key][11]
        d1['fbilltype'] = data[key][12]
        d1['fexplanation'] = data[key][13]
        d1['fupddate'] = data[key][14]
        d1['foperator'] = data[key][15]
        d1['fcheckdate'] = data[key][16]
        d1['fcheckuser'] = data[key][17]
        d1['fbillstate'] = data[key][18]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['fpartid'] = data_detail[j1][2]
                d2['fmacno'] = data_detail[j1][3]
                d2['fbuyprice'] = data_detail[j1][4]
                d2['fqty'] = data_detail[j1][5]
                d2['ftotamt'] = data_detail[j1][6]
                d2['fmemo'] = data_detail[j1][7]
                detail_list.append(d2)

        d1["details"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class SparePartTakeoutDetail(BaseModel):
    """备件领用明细新增参数"""
    fline: int = Field(..., description="行号")
    fpartid: str = Field(..., description="备件ID")
    fmacno: Optional[str] = Field("", description="设备编号")
    fbuyprice: float = Field(..., description="单价")
    fqty: float = Field(..., description="数量")
    ftotamt: float = Field(..., description="总金额")
    fmemo: Optional[str] = Field("", description="备注")


class SparePartTakeoutCreate(BaseModel):
    """备件领用新增请求参数"""
    fbillno: str = Field(..., description="领用单据编号")
    frequuser: str = Field(..., description="领用人ID")
    frequdate: str = Field(..., description="领用日期(格式需与数据库存储一致)")
    frecuser: Optional[str] = Field("", description="接收人")
    frectelno: Optional[str] = Field("", description="接收人电话")
    fsenduser: Optional[str] = Field("", description="发件人")
    fsendtype: int = Field(..., description="发送方式编码")
    fsenddate: Optional[str] = Field("", description="发送日期")
    fsendno: Optional[str] = Field("", description="快递单号")
    fbilltype: int = Field(..., description="单据类型(1-领出/2-退回)")
    foperator: str = Field(..., description="操作人ID")
    fupddate: str = Field(..., description="更新日期(格式需与数据库存储一致)")
    fbillstate: str = Field(..., description="单据状态")
    fexplanation: Optional[str] = Field("", description="领用说明")
    details: List[SparePartTakeoutDetail] = Field(..., description="领用明细列表")


@part_out_router.post("/create", summary="备件领用新增接口")
def create_part_out(create: SparePartTakeoutCreate, db: Session = Depends(get_db)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = create.fbillno
        # 返回内容
        detail_list = []
        with db.begin():
            details = create.details
            for detail in details:
                sql = "insert into lims.s_tc_partout_detail(fbillno,fline,fpartid,fmacno,fbuyprice,fqty,ftotamt,fmemo)"
                sql = sql + " values( :fbillno , :fline , :fpartid , :fmacno , :fbuyprice , :fqty , :ftotamt , :fmemo )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": detail.fline,
                    "fpartid": detail.fpartid,
                    "fmacno": detail.fmacno,
                    "fbuyprice": detail.fbuyprice,
                    "fqty": detail.fqty,
                    "ftotamt": detail.ftotamt,
                    "fmemo": detail.fmemo
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + detail.fqty
                ftotalamt = ftotalamt + detail.ftotamt
                # 返回明细
                d2 = {}
                d2['fbillno'] = fbillno
                d2['fline'] = detail.fline
                d2['fpartid'] = detail.fpartid
                d2['fmacno'] = detail.fmacno
                d2['fbuyprice'] = detail.fbuyprice
                d2['fqty'] = detail.fqty
                d2['ftotamt'] = detail.ftotamt
                d2['fmemo'] = detail.fmemo
                detail_list.append(d2)
            sql = "insert into lims.s_tc_partout_head(fbillno,frequuser,frequdate,fitemcount,fpiececount,ftotalamt,frecuser,frectelno,fsenduser,fsendtype,fsenddate,fsendno,fbilltype,fexplanation,fupddate,foperator,fbillstate)"
            sql = sql + " values( :fbillno , :frequuser , :frequdate , :fitemcount , :fpiececount , :ftotalamt , :frecuser , :frectelno , :fsenduser , :fsendtype , :fsenddate , :fsendno , :fbilltype , :fexplanation , :fupddate , :foperator , :fbillstate )"
            db.execute(text(sql), {
                "fbillno": fbillno,
                "frequuser": create.frequuser,
                "frequdate": create.frequdate,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "frecuser": create.frecuser,
                "frectelno": create.frectelno,
                "fsenduser": create.fsenduser,
                "fsendtype": create.fsendtype,
                "fsenddate": create.fsenddate,
                "fsendno": create.fsendno,
                "fbilltype": create.fbilltype,
                "fexplanation": create.fexplanation,
                "fupddate": create.fupddate,
                "foperator": create.foperator,
                "fbillstate": create.fbillstate
            })
            # 返回
            d1 = {}
            d1['fbillno'] = fbillno
            d1['frectelno'] = create.frectelno
            d1['frecuser'] = create.frecuser
            d1['frequdate'] = create.frequdate
            d1['frequuser'] = create.frequuser
            d1['fitemcount'] = fitemcount
            d1['fpiececount'] = fpiececount
            d1['ftotalamt'] = float(ftotalamt)
            d1['fsenddate'] = create.fsenddate
            d1['fsendno'] = create.fsendno
            d1['fsendtype'] = create.fsendtype
            d1['fsenduser'] = create.fsenduser
            d1['fupddate'] = create.fupddate
            d1['foperator'] = create.frequuser
            d1['fcheckdate'] = None
            d1['fcheckuser'] = None
            d1['fbillstate'] = create.fbillstate
            d1['fbilltype'] = create.fbilltype
            d1["details"] = detail_list
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@part_out_router.post("/update", summary="备件领用修改接口")
def update_part_out(update: SparePartTakeoutCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = update.fbillno
        fbillstate = update.fbillstate
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            if (fbillstate == 8):
                print("作废")
                sql = "update lims.s_tc_partout_head "
                sql = sql + " set fbillstate=8,fcheckdate=now(),fcheckuser= :userid "
                sql = sql + " where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "userid": user_id,
                })
            else:
                sql = "delete from lims.s_tc_partout_detail where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                })
                details = update.details
                for detail in details:
                    sql = "insert into lims.s_tc_partout_detail(fbillno,fline,fpartid,fmacno,fbuyprice,fqty,ftotamt,fmemo)"
                    sql = sql + " values( :fbillno , :fline , :fpartid , :fmacno , :fbuyprice , :fqty , :ftotamt , :fmemo )"
                    db.execute(text(sql), {
                        "fbillno": fbillno,
                        "fline": detail.fline,
                        "fpartid": detail.fpartid,
                        "fmacno": detail.fmacno,
                        "fbuyprice": detail.fbuyprice,
                        "fqty": detail.fqty,
                        "ftotamt": detail.ftotamt,
                        "fmemo": detail.fmemo
                    })
                    fitemcount = fitemcount + 1
                    fpiececount = fpiececount + detail.fqty
                    ftotalamt = ftotalamt + detail.ftotamt
                sql = "update lims.s_tc_partout_head "
                sql = sql + " set frequdate= :frequdate ,frequuser= :frequuser ,fexplanation= :fexplanation "
                sql = sql + ",frecuser= :frecuser ,frectelno= :frectelno ,fsenduser= :fsenduser "
                sql = sql + ",fsendtype= :fsendtype ,fsenddate= :fsenddate ,fsendno= :fsendno "
                sql = sql + ",fitemcount= :fitemcount ,fpiececount= :fpiececount ,ftotalamt= :ftotalamt "
                sql = sql + ",fupddate= :fupddate ,foperator= :foperator "
                sql = sql + " where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "frequdate": update.frequdate,
                    "frequuser": update.frequuser,
                    "fexplanation": update.fexplanation,
                    "frecuser": update.frecuser,
                    "frectelno": update.frectelno,
                    "fsenduser": update.fsenduser,
                    "fsendtype": update.fsendtype,
                    "fsenddate": update.fsenddate,
                    "fsendno": update.fsendno,
                    "fitemcount": fitemcount,
                    "fpiececount": fpiececount,
                    "ftotalamt": ftotalamt,
                    "fupddate": update.fupddate,
                    "foperator": update.foperator
                })
        return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
